package com.bluesky.light.category;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bluesky.light.util.DB;

/**
 * 這個源代碼文件是存放關於類別在數據庫中的增刪查改等操作的函數
 * @author 
 *
 */
public class CategoryDAO {
	
	/**
	 * @return
	 */
	public List<Category> getCategories() {
		List<Category> categories = new ArrayList<Category>();
		Connection conn = DB.getConn();
		Statement stmt = DB.getStatement(conn);
		String sql = "select * from t_category order by cno";
		ResultSet rs = DB.getResultSet(stmt, sql);
		try {
			while (rs.next()) {
				Category c = this.getCategoryFromRs(rs);
				categories.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}

		return categories;
	}

	/**
	 * @param rs
	 * @return
	 */
	private Category getCategoryFromRs(ResultSet rs) {
		Category c = new Category();
		try {
			c.setId(rs.getInt("id"));
			c.setPid(rs.getInt("pid"));
			c.setName(rs.getString("name"));
			c.setDescr(rs.getString("descr"));
			c.setCno(rs.getInt("cno"));
			c.setGrade(rs.getInt("grade"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}

	/**
	 * @param c
	 */
	public void add(Category c) {
		Connection conn = DB.getConn();
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		String sql = "insert into t_category (pid, name, descr, cno, grade) values (?, ?, ?, ?, ?)";
		PreparedStatement pstmt = DB.prepare(conn, sql);

		try {
			int cno = getNextCon(conn, c);
			pstmt.setInt(1, c.getPid());
			pstmt.setString(2, c.getName());
			pstmt.setString(3, c.getDescr());
			pstmt.setInt(4, cno);
			pstmt.setInt(5, c.getGrade());
			pstmt.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * @param conn
	 * @param c
	 * @return
	 */
	private int getNextCon(Connection conn, Category c) {
		int cno = -1;

		String sqlMax = "select max(cno) from t_category where pid = "
				+ c.getPid();
		Statement stmtMax = DB.getStatement(conn);
		ResultSet rsMax = DB.getResultSet(stmtMax, sqlMax);
		try {
			rsMax.next();
			int cnoMax = rsMax.getInt(1);
			// ????, ?2???????100, 3?1000
			int baseNumber = (int) Math.pow(10, Category.LEVEL_LENGTH);
			int numberToAdd = (int) Math.pow(baseNumber,
					Category.MAX_GRADE - c.getGrade());

			if (cnoMax == 0) {
				if (c.getPid() == 0) {
					cno = numberToAdd;
				} else {
					int parentCno = getParentCno(conn, c);
					cno = parentCno + numberToAdd;
				}
			} else {
				cno = cnoMax + numberToAdd;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rsMax);
			DB.close(stmtMax);
		}
		return cno;
	}

	/**
	 * @param conn
	 * @param c
	 * @return
	 */
	private int getParentCno(Connection conn, Category c) {
		int cno = -1;

		String sql = "select cno from t_category where id = " + c.getPid();
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = DB.getResultSet(stmt, sql);

		try {
			rs.next();
			cno = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
		}

		return cno;
	}

	/**
	 * @param id
	 * @return
	 */
	public Category loadById(int id) {
		Category c = null;
		Connection conn = DB.getConn();
		String sql = "select * from t_category where id = " + id;
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = DB.getResultSet(stmt, sql);

		try {
			if (rs.next()) {
				c = this.getCategoryFromRs(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}

		return c;
	}

	/**
	 * @param c
	 */
	public void update(Category c) {
		Connection conn = DB.getConn();
		String sql = "update t_category set name = ?, descr = ? where id = ?";
		PreparedStatement pstmt = DB.prepare(conn, sql);
		try {
			pstmt.setString(1, c.getName());
			pstmt.setString(2, c.getDescr());
			pstmt.setInt(3, c.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
}
